package cn.com.headfree.mouldshow.dao.impl;

import cn.com.headfree.entity.TExpertTitleInfo;
import cn.com.headfree.mouldshow.dao.TExpertTitleInfoDao;
import cn.com.headfree.vo.simple.ExpertTitleInfoVo;
import com.gta.edu.sdk.base.dao.impl.BaseDaoImpl;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository("/tExpertTitleInfoDao")
public class TExpertTitleInfoDaoImpl extends BaseDaoImpl<TExpertTitleInfo> implements TExpertTitleInfoDao {


    @Override
    public List<ExpertTitleInfoVo> findByBaseorgId(String deptId) throws Exception{
        Object[] params = new Object[1];
//        StringBuilder sql = new StringBuilder("select t.id,t.erp_no,GET_DIC('ProWorkTypes',t.major) as major,GET_DIC('ExpertLevel',t.level_name) as level_name,GET_DIC('ExpertCategory',t.type) as type,t.emp_term_start,DATE_FORMAT(t.emp_term_start,'%Y-%m-%d %H:%i:%s') as emp_term_start_str,t.emp_term_end,DATE_FORMAT(t.emp_term_end,'%Y-%m-%d %H:%i:%s') as emp_term_end_str,t.acqu_time,DATE_FORMAT(t.acqu_time,'%Y-%m-%d %H:%i:%s') as acqu_time_str,u.xb_name as user_name,GET_DIC('TalentTitle',t.emp_title) as emp_title from t_expert_title_info t INNER JOIN t_dept_emp_rela d on t.erp_no=d.erp_no INNER JOIN (select * from t_org_dept o where o.path like ?) o2 on o2.id=d.dept_id INNER JOIN t_isc_user_tmp u on t.erp_no=u.hr_erp WHERE t.is_valid='0' AND t.del_sign='0' ORDER BY t.acqu_time DESC;");
//        StringBuilder sql = new StringBuilder("select t.id,t.erp_no,GET_DIC('ProWorkTypes',t.major) as major,GET_DIC('ExpertLevel',t.level_name) as level_name,GET_DIC('ExpertCategory',t.type) as type,t.emp_term_start,DATE_FORMAT(t.emp_term_start,'%Y-%m-%d %H:%i:%s') as emp_term_start_str,t.emp_term_end,DATE_FORMAT(t.emp_term_end,'%Y-%m-%d %H:%i:%s') as emp_term_end_str,t.acqu_time,DATE_FORMAT(t.acqu_time,'%Y-%m-%d %H:%i:%s') as acqu_time_str,u.`NAME` as user_name,GET_DIC('TalentTitle',t.emp_title) as emp_title from t_expert_title_info t INNER JOIN t_dept_emp_rela d on t.erp_no=d.erp_no INNER JOIN (select * from t_org_dept o where o.path like ?) o2 on o2.id=d.dept_id LEFT JOIN isc_user u on t.erp_no=u.FAX WHERE t.is_valid='0' AND t.del_sign='0' ORDER BY t.acqu_time DESC");
//        StringBuilder sql = new StringBuilder("select t.id,t.erp_no,GET_DIC('ProWorkTypes',t.major) as major,GET_DIC('ExpertLevel',t.level_name) as level_name,GET_DIC('ExpertCategory',t.type) as type,t.emp_term_start,t.emp_term_end,t.acqu_time,u.`NAME` as user_name,GET_DIC('TalentTitle',t.emp_title) as emp_title from t_expert_title_info t INNER JOIN t_dept_emp_rela d on t.erp_no=d.erp_no INNER JOIN (select * from t_org_dept o where o.path like ?) o2 on o2.id=d.dept_id LEFT JOIN isc_user u on t.erp_no=u.FAX WHERE t.is_valid='0' AND t.del_sign='0' ORDER BY t.acqu_time DESC");
        StringBuilder sql = new StringBuilder("select t.id,t.erp_no,t.major,t.level_name,t.type,t.emp_term_start,t.emp_term_end,t.acqu_time,u.`NAME` as user_name,t.emp_title from t_expert_title_info t INNER JOIN t_dept_emp_rela d on t.erp_no=d.erp_no INNER JOIN (select * from t_org_dept o where o.path like ?) o2 on o2.id=d.dept_id LEFT JOIN isc_user u on t.erp_no=u.FAX WHERE t.is_valid='0' AND t.del_sign='0' ORDER BY t.acqu_time DESC");
        params[0] = "%"+deptId+"%";
        return this.findAllBySQL(ExpertTitleInfoVo.class, sql.toString(), null, false, params);
    }
}
